<html>
<head>
  <title>Evernote Export</title>
  <basefont face="Tahoma" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <style>
    body, td {
      font-family: Tahoma;
      font-size: 10pt;
    }
  </style>
</head>
<body>

<div>
<div><div><br></div><div><br><br><div>Creating CSV Files Using BCP and Stored Procedures</div><div><br></div><div>20 November 2006 by <a href="http://www.simple-talk.com/author/nigel-rivett/">Nigel Rivett</a> </div><div><br></div><div>This article focuses on the use of the Bulk Copy Program (BCP) to create CSV files. Although it is possible to create a CSV file using DTS or SSIS, using BCP is often simpler and more efficient.</div><p>I use <span>master..sysobjects</span> as an example table to extract.</p><h2>Create a simple CSV file</h2><p>The simplest way to copy data from a database table to file is to use the basic BCP command:</p><pre><br><span style="font-size: 10pt; font-family: 'Courier New';">BCP master..sysobjects out c:\sysobjects.txt -c -t, -T –S&lt;<i>servername&gt;</i></span></pre><p>The basic format for the BCP command for creating a CSV file is as follows:</p><p><strong>BCP &lt;table&gt; out &lt;filename&gt; &lt;switches&gt;</strong></p><p>The switches used here are:</p><ul type="disc"><li><span>-c</span> Output in ASCII with the default field terminator (tab) and row terminator (crlf)</li><li><span>-t</span> override the field terminator with &quot;,&quot;</li><li><span>-T</span> use a trusted connection. Note that U –P may be used for username/password</li><li><span>-S</span> connect to this server to execute the command</li></ul><p>Note that, like DTS/SSIS, BCP is a client utility, hence you need to supply the connection information.</p><p>For transfer of data between SQL servers, in place of <b>–c</b>, use <b>–n</b> or <b>-N</b> for native data format (<b>-N</b> = Unicode). This is much faster and avoids data conversion problems. Please refer to the previous BOL link for the complete format of the BCP command.</p><p>As BCP is a command line utility it is executed from T-SQL using xp_cmdshell. Create a directory called <b>BCP</b> on your c: drive and execute:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                c:\bcp\sysobjects.txt -c -t, -T -S'</span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><h2>Other field and row delimiters</h2><p>Often, character data includes commas which will be interpreted in the file as a field terminator. A simple way to cater for this is to use a different field delimiter which does not appear in the data. Commonly used characters are pipe (|):</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                c:\bcp\sysobjects.txt -c –t| -T -S' </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span></span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"><span style="color: fuchsia;"><br style="margin: 0cm 0cm 0pt;"><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"><span style="color: rgb(0, 0, 0);">master</span><span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span><span style="color: rgb(0, 0, 0);">@sql</span></span><br><br></span></span></pre><p>And caret (^):</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                c:\bcp\sysobjects.txt -c –t^ -T -S' </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>The terminators are not limited to a single character, so if necessary you can use (|^):</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                c:\bcp\sysobjects.txt -c –t|^ -T -S' </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>Note that this will increase the size of the file and so slow down the import/export. Another way to cater for embedded commas is to &quot;quote encapsulate&quot; character data – more about that later.</p><p>It is unusual to need to change the row terminator from the default of <strong>crlf</strong> (carriage return, line feed) but occasionally you will need the <strong>cr</strong> or <strong>lf</strong> on its own.</p><p>To do this use the hex value <strong>cr = 13 = 0x0D</strong>, <strong>lf = 10 = 0x0A</strong>. If you can't remember these values they are easily obtained, as follows:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> cr <span style="color: gray;">=</span> <span style="color: fuchsia;">ascii</span><span style="color: gray;">(</span><span style="color: red;">'</span></span><br><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span lang="EN-US" style="font-size: 10pt; color: gray; font-family: 'Courier New';">)</span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> lf <span style="color: gray;">=</span> <span style="color: fuchsia;">ascii</span><span style="color: gray;">(right(</span><span style="color: red;">'</span></span><br><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span lang="EN-US" style="font-size: 10pt; color: gray; font-family: 'Courier New';">,</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';">1<span style="color: gray;">))</span></span></pre><p>To use these in the BCP command for the row terminator the <strong>–r</strong> switch is used:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                 c:\bcp\sysobjects.txt -c -t, -r0x0D -T -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername</span></span><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>When the resulting file is opened in notepad the row terminators will not end the line – the character should appear as a non-ASCII character (a little oblong). If opened or copied to query analyser (or management studio) these will be interpreted as line breaks and the file will be more readable.</p><p>The terminator characters can be encapsulated with double quotes in the command to allow for space. I like to do this for anything other than a single character delimiter. The following example gives a crlf row terminator and | ^ field terminator.</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp master..sysobjects out<br>                 c:\bcp\sysobjects.txt -c -t&quot;| ^&quot; -r&quot;0x0D0A&quot; -T -S'<br>                                                      </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><h2>Formatting the extracted data</h2><p>If you do not require all the columns/rows, or you wish to alter the data, a simple method is to create a view. This can be used in the BCP command in the same way as a table. The following view allows you to extract just the name, the formatted create date and time and then order the results according the date created (most recent first):</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">view</span> vw_bcpMasterSysobjects<br><span style="color: blue;">as<br></span>  <span style="color: blue;">select</span> <span style="color: blue;">top</span> 100 <span style="color: blue;">percent<br></span>      <span style="color: blue;">name</span> <span style="color: gray;">,<br></span>      crdate <span style="color: gray;">=</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">,<br></span>      crtime <span style="color: gray;">=</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)<br></span>   <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br>   <span style="color: blue;">order</span> <span style="color: blue;">by</span> crdate <span style="color: blue;">desc<br></span>go<br><span style="color: blue;">declare</span> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp tempdb..vw_bcpMasterSysobjects out<br>                 c:\bcp\sysobjects.txt -c -t, -T -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>Now we can quote encapsulate the extracted data by including the formatting in the view:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">view</span> vw_bcpMasterSysobjects<br><span style="color: blue;">as<br></span>   <span style="color: blue;">select</span> <span style="color: blue;">top</span> 100 <span style="color: blue;">percent<br></span>      <span style="color: blue;">name</span> <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: blue;">name</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">,<br></span>      crdate <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">,<br></span>      crtime <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>   <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br>   <span style="color: blue;">order</span> <span style="color: blue;">by</span> crdate <span style="color: blue;">desc<br></span>go<br><span style="color: blue;">declare</span> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp tempdb..vw_bcpMasterSysobjects out <br>                 c:\bcp\sysobjects.txt -c -t, -T -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p><b>Note:<br></b><i>Quote encapsulation can also be performed by use of a format file.</i></p><h2>Complete control – stored procedures</h2><p>If you examined the BCP syntax in BOL, you might have noticed that it is possible to extract from a query by using the <span>queryout</span> keyword in place of <span>out</span>.</p><p>So, for example, an equivalent but neater version of the previous code extract would place the <span>ORDER</span> <span>BY</span> clause in the BCP statement rather than the view:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">view</span> vw_bcpMasterSysobjects<br><span style="color: blue;">as<br></span>   <span style="color: blue;">select<br></span>      <span style="color: blue;">name</span> <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: blue;">name</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">,<br></span>      crdate <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">,<br></span>      crtime <span style="color: gray;">=</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>   <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br>go<br><span style="color: blue;">declare</span> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp &quot;select * from tempdb..vw_bcpMasterSysobjects<br>                     order by crdate desc, crtime desc&quot;<br>               queryout c:\bcp\sysobjects.txt -c -t, -T -S'<br>                                             </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>Of course, strictly speaking, the view was not necessary at all as the query could have been included in the BCP statement but that can get difficult to maintain.</p><p>The <span>queryout</span> method allows you to BCP from the result of a stored procedure, which opens up a lot of possibilities and offers a lot of control over the file format. For anything other than a simple table extract I would tend to use this method rather than a view. I would also format each line within the stored procedure. This means that the formatting can be tested independently from the file creation.</p><p>Employing this technique, the above extract becomes:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">proc</span> s_bcpMasterSysobjects<br><span style="color: blue;">as<br></span>   <span style="color: blue;">select</span>   <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: blue;">name</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>   <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br>   <span style="color: blue;">order</span> <span style="color: blue;">by</span> crdate <span style="color: blue;">desc<br></span>go<br><span style="color: blue;">declare</span> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp &quot;exec tempdb..s_bcpMasterSysobjects&quot;<br>                queryout c:\bcp\sysobjects.txt -c -t, -T -S'<br>                                              </span><span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><h2>More complex formatting</h2><p>We can now change the format of the extracted data to anything we require. For example, to include column names at the top and a rowcount at the end:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">proc</span> s_bcpMasterSysobjects<br><span style="color: blue;">as<br></span>      <span style="color: blue;">set</span> <span style="color: blue;">nocount</span> <span style="color: blue;">on<br></span>      <br>      <span style="color: blue;">create</span> <span style="color: blue;">table</span> #a <span style="color: gray;">(</span>seq <span style="color: blue;">int</span><span style="color: gray;">,</span> crdate <span style="color: blue;">datetime</span><span style="color: gray;">,</span> s <span style="color: blue;">varchar</span><span style="color: gray;">(</span>1000<span style="color: gray;">))<br></span>      <span style="color: green;">-- header - column headers<br></span>      <span style="color: blue;">insert</span>      #a <span style="color: gray;">(</span>seq<span style="color: gray;">,</span> crdate<span style="color: gray;">,</span> s<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      1<span style="color: gray;">,</span> <span style="color: gray;">null,<br></span>                  <span style="color: red;">'&quot;name&quot;,&quot;crdate&quot;,&quot;crtime&quot;'<br></span>      <br>      <span style="color: green;">-- data<br></span>      <span style="color: blue;">insert</span>      #a <span style="color: gray;">(</span>seq<span style="color: gray;">,</span> crdate<span style="color: gray;">,</span> s<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      2<span style="color: gray;">,</span> crdate<span style="color: gray;">,<br></span>                          <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: blue;">name</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>                  <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>                  <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>      <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br><br>      <span style="color: green;">-- trailer - rowcount<br></span>      <span style="color: blue;">insert</span>      #a <span style="color: gray;">(</span>seq<span style="color: gray;">,</span> crdate<span style="color: gray;">,</span> s<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      3<span style="color: gray;">,</span> <span style="color: gray;">null,<br></span>                  <span style="color: red;">'rowcount = '</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>20<span style="color: gray;">),</span><span style="color: fuchsia;">count</span><span style="color: gray;">(*)-</span>1<span style="color: gray;">)<br></span>      <span style="color: blue;">from</span> #a<br><br>      <span style="color: blue;">select</span>      s<br>      <span style="color: blue;">from</span>  #a<br>      <span style="color: blue;">order</span> <span style="color: blue;">by</span> seq<span style="color: gray;">,</span> crdate <span style="color: blue;">desc</span><br>go</span></pre><p>This stored procedure will format the data as required, but when it is run via the BCP command it receives an error:</p><p><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';">&quot;Invalid object name '#a'&quot;</span></p><p>Using the profiler you can see that when using <b><span>queryout</span></b>, the query is called twice. BCP tries to get the result set format by calling the stored procedure with <span>fmtonly</span>. This does not execute the stored procedure but returns the format of the resultset. Unfortunately, it does not work if the stored procedure creates and uses a temp table. This problem can be circumvented by including a <span>set</span> <span>fmtonly</span> <span>off</span> command in the execution:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp &quot;set fmtonly off exec tempdb..s_bcpMasterSysobjects&quot;<br>                queryout c:\bcp\sysobjects.txt -c -T -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql</span></pre><p>Note that the column terminator is not used since the resultset is a single column.</p><p>---<em><strong>Editor's Note</strong></em>---<br>
This was the only piece of code that I failed to get working. The stored procedure executed fine outside the BCP, but when I tried to run the above command I received an error:</p><p><span style="font-size: 10pt; font-family: Courier New;">SQLState = HY010, NativeError = 0<br>
Error = [Microsoft][SQL Native Client]Function sequence error<br>
NULL</span></p><p>The author was unable to replicate this error. Is anyone aware of what might be causing this on my machine?<br>
---<em><strong>End Editor's Note</strong></em>---</p><p>Look at this execution using the profiler and you will see <span>set</span> <span>fmtonly</span> <span>off</span> followed by <span>set</span> <span>fmtonly</span> <span>on</span>. There is a potential problem with this though: it means that the stored procedure is executed twice, once for the format and once to extract the data – due to the <span>fmtonly</span> setting, both these calls will produce a resultset. This needs to be taken into account when considering how long the process will take and its impact on your system.</p><p>As the stored procedure is executed twice it should not contain any updates of permanent data. Particularly if data is flagged as exported then the flagging will be performed on the first call and no data will be extracted for the second call. All in all, this method should be used with caution and comments (warnings) placed in the stored procedure for the aid of future maintenance.</p><p>To get round all of these problems, use the stored procedure to insert into a table then, from the BCP, extract from that table. This gives you added features that can be valuable on systems that are performing a lot of extracts:</p><ul type="disc"><li>It gives a record of the data extracted – if the file is lost it can be recreated.</li><li>The data extracted can be presented to a user for viewing from the database.</li><li>If there are problems with the file, the support can see the data without needing access to the file.</li></ul><p>For this process, the format stored procedure, <span>s_bcpMasterSysobjects</span>, will insert into the table using an identifier and the BCP will extract using the same identifier. This process can be controlled be (a control) stored procedure which would allocate the identifier and pass it to the format stored procedure which inserted the extracted data into a table with that identifier. It then calls another stored procedure or in-line code to create files (via BCP) for each identifier which is not marked as complete. The identifier is flagged as complete after the file creation. In this way the file creation becomes independent of the extract of the data.</p><p>This identifier can be passed to both processes from a controlling stored procedure or the format stored procedure can get the next available identifier and the extract can flag the identifier as extracted – so it extracts everything that has not been extracted, allowing the export to be independent of the extract.</p><p>Which technique you use will depend on your system. The following code demonstrates the use of a controlling stored procedure:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">use</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<br>go<br><span style="color: blue;">create</span> <span style="color: blue;">table</span> Extract<br>      <span style="color: gray;">(<br></span>      Extract_ID  <span style="color: blue;">int</span> <span style="color: gray;">,<br></span>      Seq1        <span style="color: blue;">varchar</span><span style="color: gray;">(</span>100<span style="color: gray;">)</span> <span style="color: gray;">null</span> <span style="color: gray;">,<br></span>      Seq2        <span style="color: blue;">varchar</span><span style="color: gray;">(</span>100<span style="color: gray;">)</span> <span style="color: gray;">null</span> <span style="color: gray;">,<br></span>      Seq3        <span style="color: blue;">varchar</span><span style="color: gray;">(</span>100<span style="color: gray;">)</span> <span style="color: gray;">null</span> <span style="color: gray;">,<br></span>      Data        <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)</span> <span style="color: gray;">,<br></span>      InsertDate  <span style="color: blue;">datetime</span> <span style="color: blue;">default</span> <span style="color: fuchsia;">getdate</span><span style="color: gray;">()<br></span>      <span style="color: gray;">)<br></span>go<br><span style="color: blue;">create</span> <span style="color: blue;">proc</span> s_bcpMasterSysobjects<br>@ExtractID <span style="color: blue;">int<br>as<br>declare</span> @rowcount <span style="color: blue;">int<br></span>      <span style="color: blue;">set</span> <span style="color: blue;">nocount</span> <span style="color: blue;">on<br></span>      <br>      <span style="color: green;">-- header - column headers<br></span>      <span style="color: blue;">insert</span>      Extract <span style="color: gray;">(</span>Extract_ID<span style="color: gray;">,</span> Seq1<span style="color: gray;">,</span> Data<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      @ExtractID<span style="color: gray;">,<br></span>                  <span style="color: red;">'01'</span> <span style="color: gray;">,<br></span>                  <span style="color: red;">'&quot;name&quot;,&quot;crdate&quot;,&quot;crtime&quot;'<br></span>      <br>      <span style="color: green;">-- data<br></span>      <span style="color: blue;">insert</span>      Extract <span style="color: gray;">(</span>Extract_ID<span style="color: gray;">,</span> Seq1<span style="color: gray;">,</span> Seq2<span style="color: gray;">,</span> Data<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      @ExtractID<span style="color: gray;">,<br></span>                  <span style="color: red;">'02'</span> <span style="color: gray;">,<br></span>                  <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>100<span style="color: gray;">),</span> <span style="color: red;">'99990101'</span> <span style="color: gray;">-</span> crdate<span style="color: gray;">,</span> 121<span style="color: gray;">)</span> <span style="color: gray;">,<br></span>                          <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: blue;">name</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>                  <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 112<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>                  <span style="color: gray;">+</span> <span style="color: red;">','</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>8<span style="color: gray;">),</span> crdate<span style="color: gray;">,</span> 108<span style="color: gray;">)</span> <span style="color: gray;">+</span> <span style="color: red;">'&quot;'<br></span>      <span style="color: blue;">from</span> master<span style="color: gray;">..</span>sysobjects<br><br>      <span style="color: blue;">select</span> @rowcount <span style="color: gray;">=</span> <span style="color: fuchsia;">@@rowcount<br></span>      <br>      <span style="color: green;">-- trailer - rowcount<br></span>      <span style="color: blue;">insert</span>      Extract <span style="color: gray;">(</span>Extract_ID<span style="color: gray;">,</span> Seq1<span style="color: gray;">,</span> Data<span style="color: gray;">)<br></span>      <span style="color: blue;">select</span>      @ExtractID<span style="color: gray;">,<br></span>                  <span style="color: red;">'03'</span> <span style="color: gray;">,<br></span>                  <span style="color: red;">'rowcount = '</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">varchar</span><span style="color: gray;">(</span>20<span style="color: gray;">),</span>@rowcount<span style="color: gray;">)<br></span>go<br><span style="color: blue;">create</span> <span style="color: blue;">proc</span> s_Extract<br>@ExtractID <span style="color: blue;">int<br>as<br></span>      <span style="color: blue;">select</span>      Data<br>      <span style="color: blue;">from</span>  Extract<br>      <span style="color: blue;">where</span> Extract_ID <span style="color: gray;">=</span> @ExtractID<br>      <span style="color: blue;">order</span> <span style="color: blue;">by</span> Seq1<span style="color: gray;">,</span> Seq2<span style="color: gray;">,</span> Seq3<br>go</span></pre><p>Now the data is extracted via:</p><pre><br><span style="font-size: 10pt; font-family: 'Courier New';"><span style="color: rgb(0, 0, 255);">Create</span> table ExportLog (Export_id int, Status varchar(20))<br><br></span><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">Insert </span><span style="font-size: 10pt; font-family: 'Courier New';">ExportLog select 25, </span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span style="font-size: 10pt; font-family: 'Courier New';">Extracting</span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';"><br>exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<span style="color: gray;">..</span>s_bcpMasterSysobjects 25<br><span style="color: blue;">update </span></span><span style="font-size: 10pt; font-family: 'Courier New';">ExportLog set Status = </span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span style="font-size: 10pt; font-family: 'Courier New';">Exporting</span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span style="font-size: 10pt; font-family: 'Courier New';"> where Export_id = 25</span><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';"><br>declare</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> @sql <span style="color: blue;">varchar</span><span style="color: gray;">(</span>8000<span style="color: gray;">)<br></span><span style="color: blue;">select</span> @sql <span style="color: gray;">=</span> <span style="color: red;">'bcp &quot;exec tempdb..s_Extract 25&quot;<br>                queryout c:\bcp\sysobjects.txt -c -T -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span><span style="color: blue;">exec</span> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> @sql<br><span style="color: blue;">update </span></span><span style="font-size: 10pt; font-family: 'Courier New';">ExportLog set Status = </span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span style="font-size: 10pt; font-family: 'Courier New';">complete</span><span lang="EN-US" style="font-size: 10pt; color: red; font-family: 'Courier New';">'</span><span style="font-size: 10pt; font-family: 'Courier New';"> where Export_id = 25</span></pre><p>You can view the data extracted via:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> tempdb<span style="color: gray;">..</span>s_Extract 25</span></pre><p>Alternatively, you can use the following, which will also give the date that the data was extracted:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> <span style="color: gray;">*</span> <span style="color: blue;">from</span> tempdb<span style="color: gray;">..</span>Extract <span style="color: blue;">order</span> <span style="color: blue;">by</span> Seq1<span style="color: gray;">,</span> Seq2<span style="color: gray;">,</span> Seq3</span></pre><p><b>Note:<br></b><i>For the extract I have given three sort columns Seq1, Seq2, Seq3 which are all order ascending. This means that the extracting stored procedure needs to place the values in ascending order (see the date manipulation). This could have ascending and descending sort columns or the use could be dependent on the extract type.</i></p><p>For a system which is performing a lot of small exports I will usually implement this process, and a similar process for imports.</p><h2>Extracting all tables from a database</h2><p>This is usually performed to transfer data between servers/databases and so should use the native data format. The easiest way is to create the BCP statements from a query then execute them:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">select</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> <span style="color: red;">'exec master..xp_cmdshell'</span> <br>            <span style="color: gray;">+</span> <span style="color: red;">' '''<br></span>            <span style="color: gray;">+</span> <span style="color: red;">'bcp'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">' '</span> <span style="color: gray;">+</span> TABLE_CATALOG <span style="color: gray;">+</span> <span style="color: red;">'.'</span> <span style="color: gray;">+</span> TABLE_SCHEMA <span style="color: gray;">+</span> <span style="color: red;">'.'</span> <span style="color: gray;">+</span> TABLE_NAME <br>            <span style="color: gray;">+</span> <span style="color: red;">' out'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">' c:\bcp\'<br></span>            <span style="color: gray;">+</span> TABLE_CATALOG <span style="color: gray;">+</span> <span style="color: red;">'.'</span> <span style="color: gray;">+</span> TABLE_SCHEMA <span style="color: gray;">+</span> <span style="color: red;">'.'</span> <span style="color: gray;">+</span> TABLE_NAME <span style="color: gray;">+</span> <span style="color: red;">'.bcp'</span> <br>            <span style="color: gray;">+</span> <span style="color: red;">' -N'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">' -T'<br></span>            <span style="color: gray;">+</span> <span style="color: red;">' -S'</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">@@servername<br></span>            <span style="color: gray;">+</span> <span style="color: red;">''''<br></span><span style="color: blue;">from</span> <span style="color: green;">INFORMATION_SCHEMA.TABLES<br></span><span style="color: blue;">where</span> TABLE_TYPE <span style="color: gray;">=</span> <span style="color: red;">'BASE TABLE'</span></span></pre><p>The result of this will be a series of BCP statements of the form:</p><pre><br><span lang="EN-US" style="font-size: 10pt; color: blue; font-family: 'Courier New';">exec</span><span lang="EN-US" style="font-size: 10pt; font-family: 'Courier New';"> master<span style="color: gray;">..</span><span style="color: maroon;">xp_cmdshell</span> <span style="color: red;">'bcp tempdb.dbo.Extract out<br>                           c:\bcp\tempdb.dbo.Extract.bcp -N -T –S&lt;servername&gt;'</span></span></pre><p>which will extract all tables in the database. To import just change the &quot;out&quot; to &quot;in&quot;.</p><h2>Summary</h2><p>We have seen how to use BCP to perform a simple extract of a table to a CSV file, how to perform slightly more complex extracts using a view, extracting using a stored procedure to format the data and a system which allows the data to be extracted and saved in a table and viewed/exported from there.</p><br><br>
This article has been viewed 238084 times.</div><div><a href="http://www.simple-talk.com/author/nigel-rivett/" title="Nigel Rivett"><img align="left" alt="Nigel Rivett" style="cursor:default;vertical-align:middle;"></img></a><p><strong>Author profile:</strong> <a href="http://www.simple-talk.com/author/nigel-rivett/">Nigel Rivett</a></p><p>Nigel spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server for about 10 years from v4.2 to v2005, and was awarded Microsoft MVP status in 2003. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.</p><p><a href="http://www.simple-talk.com/author/nigel-rivett/">Search for other articles by Nigel Rivett</a></p></div><div><strong>Rate this article:</strong>   Avg rating: <img style="cursor:default;vertical-align:middle;"></img> from a total of 222 votes.<br><br><div><table border="0"><tbody><tr><td><br>
Poor</td><td><br>
OK</td><td><br>
Good</td><td><br>
Great</td><td><br>
Must read</td><td></td></tr></tbody></table></div></div><div><a name="forum"> </a><div><div>Have Your Say</div>
Do you have an opinion on this article? Then <a href="http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/#commentform"><u>add your comment below</u></a>:</div><div><div style="width: 100%;">You must be logged in to post to this forum<br><br><a href="http://www.simple-talk.com/community/login.aspx?ReturnUrl=%2fsql%2fdatabase-administration%2fcreating-csv-files-using-bcp-and-stored-procedures%2f">Click here to log in</a>.</div><br><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Nigel does it again!</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, November 20, 2006 at 3:54 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I loved this article. Heavens only knows how many articles I've read on BCP but here was definitely something new here. I'd never thought of the 'set fmtonly' issue, and the verious work-rounds.<br><br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Using BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Phil Factor</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=2126">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, November 21, 2006 at 8:23 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Nigel,<br>
Great article.<br>
BCP is a wonderful trip down memory lane. Are there, as I suspect, good performance reasons for using BCP over the other tools that Microsoft now provide? It certainly is guaranteed to work on any version!</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re: Using BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, November 22, 2006 at 11:50 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>bcp should be the fastest method for transferring text files (or bulk insert for importing).<br><br>
I would always use it for small files for speed of development. If there are a lot of files to deal with then the package load time (especially with SSIS) can be the biggest overhead.<br><br>
There are times that bcp isn't so useful though. If run from a stored proc then it is easy to run it on the server but not so easy from a remote machine (not that difficult though) - but then you would need a method to run the other applications too.<br>
I try to stay away from format files as I like to keep everything in one place.<br>
If data needs to be transformed then that may be simpler, faster or take less resources in a package than using bcp.<br><br>
One reason I dislike DTS is that people tend to include flow control and unrelated tasks in a single package and lose the separation of processes.<br>
SSIS has the same problem but is a more complete application - just needs someone with a bit of system design experience to oversee what is being done.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Using BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, November 29, 2006 at 5:08 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Let me qualify my comment by saying I am a SQL developer and a big fan of using BCP. It is a fast and customizable way to move data around. I can do much more with the data from BCP than I can with most tools.<br><br>
However, every DBA that I have worked with says BCP leaves open a gaping security whole. Applications and users that have access to xp_cmdshell manipulate the machine that is running the system. The comment I hear most often is that nobody but the NA and DBA need access to the machine the database runs on.<br><br>
Your thoughts?</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Nice one</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, December 04, 2006 at 9:11 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Great article!<br><br>
The only drawback of BCP exporting is that sometimes you'd need column names for the data and BCP doesn't give you that.<br><br>
I've coverd this a while ago:<br>
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx<br><br>
Mladen</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>reply</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, December 05, 2006 at 9:40 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>&gt;&gt; The only drawback of BCP exporting is that sometimes you'd need column names for the data and BCP doesn't give you that.<br><br>
That's covered in the article - one of the formatting SPs gives column names in the first row.<br><br>
&gt;&gt; The comment I hear most often is that nobody but the NA and DBA need access to the machine the database runs on.<br>
That's true - in a production environment this sort of thing would usually be run from the scheduler using sa (or some user with sa permissions).<br>
Also remember that bcp is a client utility - you can connect to a remote server to save the file locally - although that will be slower due to the network. Often useful to run the bcp on a local sql server but connect to a remote server for the data.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>I think something is missed</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, December 05, 2006 at 12:26 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>How about doesnt have a field terminator?<br><br>
The terminators are not limited to a single character, so if necessary you can use (|^)<br><br>
But in case i dont want terminators , how can i do?<br><br>
C. Lages<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>No field terminator</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, December 06, 2006 at 4:35 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>If by that you mean fixed field length then you can just use the format sp to concatenate the fields with no separator.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Bad solution</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, January 10, 2007 at 12:34 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Using xp_cmdshell and bcp in a sproc is not a good solution. It's not secure. Learn how to use DTS or write a .Net program to do this. PUshing data out of the server with a sproc is bad. Massive security issue.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re: bad solution</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Phil Factor</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=2126">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, January 11, 2007 at 12:29 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>The systems I write make use of xp_cmdshell, and I use routines very like Nigels. In fact I pinched one or two from him.<br>
I use them in what I have always believed to be a perfectly secure way. The task goes into a 'queue' (simply a table treated as a LIFO) which is polled by a stored procedure called by the SQL Server Scheduler. The particular step in the job, or the entire job can be run as a specified user with the rights, and just the rights, necessary to perform the operation. If an operation needs to be performed, it is done so under, or as, that user. Only one action can be performed, and it is a simple matter to ensure that the parameters that are provided in the queue conform to legal specifications. The user is, of course, only used by the scheduled step or job.<br>
I'm struggling to work out how this is insecure. Can you please elaborate, as I'd hate to have to stop using methods like this, when I don't understand the security loophole.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Bad solution</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Saturday, January 13, 2007 at 9:32 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Think you're misunderstanding the concepts.<br>
bcp is just a tool (a command line executable) used to extract the data. I am calling it using xp_cmdshell here because it is usually simplest. Of course in some environments that is not avalable (in the same way that dts/ssis might not be available for exactly the same reasons).<br><br>
There is usually a way around this e.g. set up another server to run the aplication (whether it's bcp, dts, ssis).<br><br>
Not that these solutions are all client applications and all have the same limitations - they can all be executed by xp_cmdshell but all of them can be executed by other means so that is not an issue.<br><br>
The point is to use the method that is best for the objective. Often people will automatically go for dts or ssis (not usually a .net app but...) whereas it can be a lot simpler and faster using other methods. Sure in some environments it may be more complicated to set up and that may change the decision but the important think is to know what is available and the pros and cons, and not to dismiss a solution just because it might not be the best in another situation.<br><br>
Dts I feel is not a particulaly good product and vastly overused by application developers who want to dabble in database soltions.<br>
SSIS I feel to be a lot better but again tends to be used as an isolated application builder rather than as one of a suite of tools to build a system.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>bcp column display in .csv</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, February 09, 2007 at 5:29 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Using bcp command how i display the output data with the column names?</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Import text file data into sql server table</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Saturday, February 17, 2007 at 2:50 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br>
I read your article and i'm much impressed reading it. I need your expert suggestion/help in solving my task. I need to import around 200 text files data into sql server table. Can you suggest me how this can be done. I really appreciate your help in this regard.<br>
Thanks,<br>
Hemal<br>
hemal_s_shah@yahoo.ca</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>SQL to Access</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Sunday, February 25, 2007 at 6:13 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Say I have to export data from MS-SQL to an Access database. How do I do that? I know that you can use an intermediary text file but the problem is that I don't know of a method that will import that file in an authomated manner (not manually) into the Access database.<br><br>
I ask because in my situation I cannot use a DTS package because the client that has to use the export/import facility doesn't have installed the SQL server and the DtsRun utility for that matter.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>SQL to Access</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, February 26, 2007 at 8:12 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Say I have to export data from MS-SQL to an Access database. How do I do that? I know that you can use an intermediary text file but the problem is that I don't know of a method that will import that file in an authomated manner (not manually) into the Access database.<br><br>
I ask because in my situation I cannot use a DTS package because the client that has to use the export/import facility doesn't have installed the SQL server and the DtsRun utility for that matter.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Thanks Nigel ..</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, March 06, 2007 at 12:47 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>One of the best articles I've ever read.<br>
Thanks Nigel.<br>
You have kept the name 'simple-talk'.<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Good article</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, March 27, 2007 at 9:04 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I ended up finding this article by searching for &quot;SQLState = HY010, NativeError = 0&quot; and catching the editor's note. My error was using a #tmp file. I repeated the editor's error when I tried your fix. Fixed that problem by making the #tmp a &quot;(select ... from ...) a&quot; result set.<br><br>
Ran into another problem - bcp will only accept the first result set output. (Produced only 1 output result set at a time, but the second one blew up.) Had to split the sproc into two different sprocs.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Error</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, April 25, 2007 at 8:00 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>SQLState = 08001, NativeError = 17<br>
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.<br>
SQLState = 01000, NativeError = 53<br>
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).<br>
NULL<br><br>
Im getting this message when I try to run Procedutre in Server.. any help<br><br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re - Error</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, April 25, 2007 at 12:40 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>What are you trying to run - can you post the command.<br>
The error is saying that you cannot connect to the server.<br>
Do you have a space or suchlike in the server\instance name?<br><br>
Can you post the command that is being executed.<br>
You can use the profiler to see if the server is being contacted and refusing the login.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Just doesn't work</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Sunday, May 06, 2007 at 11:30 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Dear Nigel,<br><br>
You've done a wonderful job laying out everything and I think you explain everything very clearly.<br><br>
However, just doesn't work for me. I'm a newbie and using SQL 2005 express and the follwoing just doesn't work for me. See:<br><br>
use s300<br>
go<br>
if exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS<br>
WHERE TABLE_NAME = 'vw_XRef')<br>
drop view vw_XRef<br>
go<br><br>
create view vw_XRef<br>
as<br>
SELECT *<br>
FROM s300.dbo.UAC5DPS3<br>
go<br><br>
declare @sql varchar(1000)<br>
select @sql = 'bcp s300..vw_XRef out<br>
c:\BeneSys\xRef.txt -c -t, -T -S'+ @@servername<br>
exec master..xp_cmdshell @sql<br><br>
Add I receive the following message:<br><br>
usage: bcp {dbtable | query} {in | out | queryout | format} datafile<br>
[-m maxerrors] [-f formatfile] [-e errfile]<br>
[-F firstrow] [-L lastrow] [-b batchsize]<br>
[-n native type] [-c character type] [-w wide character type]<br>
[-N keep non-text native] [-V file format version] [-q quoted identifier]<br>
[-C code page specifier] [-t field terminator] [-r row terminator]<br>
[-i inputfile] [-o outfile] [-a packetsize]<br>
[-S server name] [-U username] [-P password]<br>
[-T trusted connection] [-v version] [-R regional enable]<br>
[-k keep null values] [-E keep identity values]<br>
[-h &quot;load hints&quot;] [-x generate xml format file]<br>
NULL<br><br>
Can anyone lend me some direction?</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>An error message when there is a invalid object while creating Stored Procedure.</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 09, 2007 at 2:26 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Is there any keyword to display an error message when there is a invalid object while creating Stored Procedure.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Error Generation at time of creating Store Procedure</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 09, 2007 at 2:38 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Can we display an error message if a Stored Procedure contains invalid objects at the time of creating the stored procedure.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Solution for &quot;Function sequence error&quot; in editor's note</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 09, 2007 at 6:35 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I got the same error as in the editor's note:<br><br>
SQLState = HY010, NativeError = 0<br>
Error = [Microsoft][SQL Native Client]Function sequence error<br>
NULL<br><br>
I able to solve it by turn on the Database Mail under Surface Area Configuration for Features<br><br>
Here are the details:<br>
http://yytechblog.com/2007/05/09/function-sequence-error-in-bcp-with-xp_cmdshell/</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Excellent</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, May 14, 2007 at 9:43 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Thanks so much - learned a few very useful techniques!</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>help needed</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 23, 2007 at 2:48 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Is there any possiblity to increase the length of the Work Sheet in Excel or is there any possiblity to have a different name for the Work sheet?</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Excellent</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 23, 2007 at 6:11 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Thanks so much - learned a few very useful techniques!</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Unknown argument 'ûS...'</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, May 23, 2007 at 6:14 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I get the following error with the -S parameter. Any idea? MySqlServer is my sql server name.<br><br>
C:\&gt;BCP Northwind..Employees out c:\Temp\bcp_csv_<br>
northwindEmployees.txt -c -t, -T -SMySqlServer<br><br>
Unknown argument 'ûSMySqlServer' on command line.<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Excellent</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, May 25, 2007 at 4:03 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I am trying to create a .xls file using bcp command and i am successfully able to create. But i need create a multiple sheets in a single .xls file.<br><br>
Thanks in Advance</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>how you can load an XML file directly into sql table</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Ramesh babu</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, June 01, 2007 at 4:55 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>plz help me</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Solution for &quot;Function sequence error&quot; in editor's note</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Dmytro Andriychenko</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, June 04, 2007 at 8:52 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Had a problem with this error:<br><br>
SQLState = HY010, NativeError = 0<br>
Error = [Microsoft][SQL Native Client]Function sequence error<br><br>
coming up when using bcp. The problem seem to occure only with SQL 2005 version of bcp.exe. The version of bcp.exe from SQL 2000 runs fine! What one can do is simply use the old version until MS comes up with a fix. All you need is bcp.exe and bcp.rll from SQL 2000 installation.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re: how you can load an XML file directly into sql table</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Phil Factor</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=2126">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, June 04, 2007 at 3:13 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I've dealt with this question in the forums:<br><br>
http://www.simple-talk.com/community/forums/ShowThread.aspx?PostID=30267#30796</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Column name</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Dale</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, June 12, 2007 at 1:33 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Is the method covered in the article the only one that can be used to show the column names at the top of the file?<br><br>
Are there simpler way? Using the presented method would be too heavy in the long run.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Export SQL to XML</span></b></td></tr><tr><td>Posted by:</td><td><i><span>russt</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, July 04, 2007 at 10:54 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br><br>
I am new to SQL stored procedure and I would like to know how to export save an SQL table to XML using bcp?<br><br>
Cheers.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Export SQL to XML</span></b></td></tr><tr><td>Posted by:</td><td><i><span>russt</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, July 04, 2007 at 10:56 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br><br>
I am new to SQL stored procedure and I would like to know how to export save an SQL table to XML using bcp?<br><br>
Cheers.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re: Export SQL to XML</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, July 05, 2007 at 5:23 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>You can format the sql in the stored proceudure or have a look at sp_makewebtask</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re: Column name</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, July 05, 2007 at 5:25 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Try it - you might be surprised at how efficient it is.<br><br>
Otherwise have a look at SSIS (or dts) or osql.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Thankyou Nigel Rivett</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Pastone</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Sunday, July 29, 2007 at 3:28 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Thanks for a good article on BCP. It has given me some ideas for a project I'm currently working on.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>what is BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Saturday, August 18, 2007 at 1:57 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I want to know wat is BCP and how it is used to create CSV file frm Table<br>
Pls if u give code how to use it</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>what is BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Saturday, August 18, 2007 at 2:45 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I want to know wat is BCP and how it is used to create CSV file frm Table<br>
Pls if u give code how to use it</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>re what is BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, September 07, 2007 at 6:53 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>BCP is bulk copy program.<br>
&gt;&gt; how it is used to create CSV file frm Table<br>
Well that's what the article is about and there's lot's of code examples there.<br><br>
If you don't want to use a query then just change the quetry to the table name and use out instead of queryout.<br>
And have a look in books online.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>oops</span></b></td></tr><tr><td>Posted by:</td><td><i><span>nigelrivett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3323">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, September 07, 2007 at 7:01 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>sorry about the apostrophe and t and missing o.<br>
And the syntax I guess.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>column separator quoting</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Curtis</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3301">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, September 14, 2007 at 4:18 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>A very helpful article. Thank you.<br><br>
However, I think I missed something. How does one gracefully deal with quoting embedded separator characters? Some of the fields I'm dumping are free-text entry, so they may have commas in them, but I need to produce a .csv file for each table to feed to mysqlimport. The db schema changes periodically, so I don't want to have to create (and maintain) a view for every table I'm exporting, just to escape every column. Most .csv creation tools automatically quote and escape values... how do I get bcp to behave nicely?<br><br>
Thanks?<br>
Curtis<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Vertical bar doesn't work</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Steve H</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Sunday, September 30, 2007 at 11:15 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Good day.<br><br>
I just saw this article yesterday and it is precisely what I need. I am trying to work with the limitations of SSIS and need to use the bcp out using the sp_cmdshell. I want to use a single stored procedure to process x number of data exports by passing in the parameters.<br><br>
I have constructed a test and if I use a comma as the delimiter (or for that matter, almost any character, it works like a dream. However, if I use a vertical bar as follows:<br><br>
Declare @outputstring varchar(8000)<br>
SELECT @outputstring = 'bcp sds.dbo.TSDS_VND_EXP out ' +<br>
'\\Euterpe\data\gap\SDS_Data\essbase_sourcing\Sourcing_Vendor.txt -c -t| -T -S'+ @@servername<br><br>
exec master..xp_cmdshell @outputstring<br><br>
I get the following output error:<br><br>
'-T' is not recognized as an internal or external command,<br>
operable program or batch file.<br>
NULL<br><br>
Now, I remember in the dark recesses of my mind that I had this difficulty many years ago and that there is an OS level setting that needs to be changed. But I have no recollection of what that might be.<br><br>
Anyone have any ideas?<br><br>
Thank you.<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>pipe char</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Jesse</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, October 04, 2007 at 3:05 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>try -t^| instead of -t|<br><br>
| is a special char in the shell, used for pipes, so it has to be escaped.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>no file found</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, November 28, 2007 at 1:56 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>i have this code..<br><br>
DECLARE @FileName varchar(50),<br>
@bcpCommand varchar(2000)<br><br>
SET @FileName = REPLACE('c:\trial.txt','/','-')<br><br>
SET @bcpCommand = 'bcp &quot;SELECT * FROM pubs..authors ORDER BY au_lname&quot; queryout &quot;'<br>
SET @bcpCommand = @bcpCommand + @FileName + '&quot; -Usa -Ppassword -c'<br><br>
EXEC master..xp_cmdshell @bcpCommand<br><br><br><br>
.......but why cant i find the output file (trial.txt)? what is the problem?<br><br>
thanks.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>great article</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, December 24, 2007 at 5:56 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Thanks for useful examples.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>set fmtonly</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Hawkins Dale</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, January 08, 2008 at 9:46 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>This article saved my butt.<br><br>
I had a bcp process in production that was creating the expected extract properly. I had to add a new column, which meant rewriting the stored procedure so as to use a temp table (a table variable, actually), and the stupid thing broke: it would only return the first column.<br><br>
Sticking SET FMTONLY OFF in front of the EXEC fixed it.<br><br>
Thanks!<br></span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>BCP</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, January 10, 2008 at 9:58 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hey. I'm having issues with the BCP statement. Here is my statement<br><br>
bcp &quot;SELECT Number from ACHAData..Number&quot; queryout c:\Contact.txt -c -T<br><br>
It sits in stored procedure(which has no other lines of code ocntained in it) I get the following error<br><br>
Incorrect syntax near 'queryout'.<br><br>
Any thoughts?</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>delimiter</span></b></td></tr><tr><td>Posted by:</td><td><i><span>hizakemi</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=11762">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, February 12, 2008 at 8:01 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hello--<br><br>
I need to Ç as my field delimiter and Æ as my row delimiter in bcp script. I use the script below but I dont get the right outcome- HELP<br><br>
bcp databse.dbo.table out table.dat -S myserver -U myserid -P mypassword -c -tÇ -r&quot;Æ&quot;<br><br>
Josephine</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Thanks</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Anonymous</span></i> (<span>not signed in</span>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, March 19, 2008 at 2:30 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Thanks! For those who are not technical enough, you may look for www.sqlscripter.com to export data to Csv.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Is it possible to add column heading with BCP?</span></b></td></tr><tr><td>Posted by:</td><td><i><span>chrissclee</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=3499">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, November 11, 2008 at 1:40 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br><br>
Is there way to include column in CSV file when using BCP? Thanks.<br><br>
Chris</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Convert CSV to Text file</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Ramanathan</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=12217">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, November 19, 2008 at 6:53 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>hi<br><br>
I thank to you for explaining a .csv file convert to .txt with some delimiters vise versa. good works.<br><br>
with regards.<br>
K.Ramanathan<br>
ramji_ramanathan@yahoo.com</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Very well done!</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Jeff Moden</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=5853">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, February 27, 2009 at 11:43 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I was trying to same myself some time by looking for examples of the different uses of the BCP command to show the folks at work. I believe I've just found one of the most comprehensive set of examples I could have asked for. The big bonus was learning the trick to address temp tables... that's gonna be a huge time saver for me.<br><br>
Thanks for the great article, Nigel.</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>No file 2</span></b></td></tr><tr><td>Posted by:</td><td><i><span>JusticeV</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=21170">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, December 22, 2009 at 11:51 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Having the same issue another here had where I get:<br><br>
Add I receive the following message:<br><br>
usage: bcp {dbtable | query} {in | out | queryout | format} datafile<br>
[-m maxerrors] [-f formatfile] [-e errfile]<br>
[-F firstrow] [-L lastrow] [-b batchsize]<br>
[-n native type] [-c character type] [-w wide character type]<br>
[-N keep non-text native] [-V file format version] [-q quoted identifier]<br>
[-C code page specifier] [-t field terminator] [-r row terminator]<br>
[-i inputfile] [-o outfile] [-a packetsize]<br>
[-S server name] [-U username] [-P password]<br>
[-T trusted connection] [-v version] [-R regional enable]<br>
[-k keep null values] [-E keep identity values]<br>
[-h &quot;load hints&quot;] [-x generate xml format file]<br>
NULL<br><br>
But no file created.<br><br>
Any help would be very appreciated</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>using bcp with temp files</span></b></td></tr><tr><td>Posted by:</td><td><i><span>john.campbell</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22469">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, February 10, 2010 at 8:22 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>I have found that in order to bcp out a #temp file from a sp the temp table needs to be declared as a global temp table ##temp. The scope of a #temp table is local and only readable in that query window or sp, whereas the bcp command is a dos command and does not have access to the #temp file, whereas it can the ##temp file just fine. When using this I always search for the ##temp file at the beginning of the sp and kill them, since they can hang around if there are communications problems between servers.<br>
Great article!</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Is there a workround</span></b></td></tr><tr><td>Posted by:</td><td><i><span>mbouarroudj</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22475">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, February 10, 2010 at 11:33 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi Nigel and thanks for this vey nice article<br><br>
Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?<br><br>
Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?<br><br>
Thanks</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Is there a workround</span></b></td></tr><tr><td>Posted by:</td><td><i><span>mbouarroudj</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22475">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, February 10, 2010 at 12:40 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi Nigel and thanks for this vey nice article<br><br>
Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?<br><br>
Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?<br><br>
Thanks</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Is there a workround</span></b></td></tr><tr><td>Posted by:</td><td><i><span>mbouarroudj</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22475">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Wednesday, February 10, 2010 at 2:30 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi Nigel and thanks for this vey nice article<br><br>
Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?<br><br>
Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?<br><br>
Thanks</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Is there a workround</span></b></td></tr><tr><td>Posted by:</td><td><i><span>mbouarroudj</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22475">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, February 11, 2010 at 10:26 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi Nigel and thanks for this vey nice article<br><br>
Is there any work-around to prevent the stored to be executed twice, I'm facing a performance issue due to this beghavior?<br><br>
Is there any blog or site managed by Microsoft SQL Server team were I can post this issue?<br><br>
Thanks</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Column Names</span></b></td></tr><tr><td>Posted by:</td><td><i><span>maxbrackett</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22519">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Thursday, February 11, 2010 at 4:02 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>If column names are not going to be dynamic, this works nicely....<br><br>
&quot;SELECT ''Column1'', ''Column2'', ''Column3'' UNION SELECT Column1, Column2, Column3 FROM Table&quot;<br><br>
Thanks for the page, great help!<br>
Max</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>About BCP, a question from SQL2005 Migration</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Lina</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22605">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Monday, February 15, 2010 at 12:29 PM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>In our original DTS jobs, we use Stored Procedures and run BCP commands to Import/Export files with data manipulating steps. In our new SQL 2005 environment we can not have the permission for Admin and BCP. We can not use BULK INSERT neither because of the networking access.<br><br>
I know we can use SSIS TRANSFORM, but I need to &quot;chop&quot; Stored Procedures to pieces (data manipulating steps) to insert the SSIS TRANSFORM task, we have about 400 hundreds Stored Procedures. It sounds crazy.<br><br>
Do you think if there is any other ways to work for the situation ?<br><br>
Thank you so much for your help.<br>
Lina</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Informative Topic;</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Matt John</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22883">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, February 26, 2010 at 1:10 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>very healthy material found in this topic, i really gained so much information about that,<br><br>
Matt John<br>
-- ---- ----<br>
&lt;a href=&quot;http://www.microsoft-exam-guides.com&quot;&gt;microsoft exams&lt;/a&gt;</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Informative Topic ---&gt;</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Matt John</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=22883">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Friday, February 26, 2010 at 1:11 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br>
very healthy material found in this topic, i really gained so much information about that,<br><br>
Matt John<br>
-- ---- ----<br>
[url=http://www.microsoft-exam-guides.com]microsoft exams[/url]</span></td></tr></tbody></table><hr><table border="0" cellpadding="0" cellspacing="4" width="500"><tbody><tr><td>Subject:</td><td><b><span>Regarding error Function sequence error</span></b></td></tr><tr><td>Posted by:</td><td><i><span>Prasoon.1983</span></i> (<a href="http://www.simple-talk.com/community/user/Profile.aspx?UserID=28658">view profile</a>)</td></tr><tr><td>Posted on:</td><td><i><span>Tuesday, July 20, 2010 at 1:18 AM</span></i></td></tr><tr><td valign="top">Message:</td><td><span>Hi,<br><br>
When I used &quot;set fmtonly off &quot; with stored procedure as query to bcp, I got the same error &quot;Function sequence error&quot; but then I made the below changes to my SP to get it worked.<br><br>
1. The furst statement in SP should be a SELECT statement.<br>
2. Insert, Update statement should come after SELECT.<br>
3. DECLARE or SET statements are fine before the SELECT.<br><br>
With warm regards<br>
Prasoon Kumar Gupta</span></td></tr></tbody></table><hr><a name="commentform"> </a></div></div><div><div><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/">SQL Home</a></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/learn-sql-server/">Learn SQL Server</a> <span>(68 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/sql-training/">SQL Training</a> <span>(1 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/database-administration/">Database Administration</a> <span>(88 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/t-sql-programming/">T-SQL Programming</a> <span>(118 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/performance/">Performance</a> <span>(31 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/backup-and-recovery/">Backup and Recovery</a> <span>(18 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/sql-tools/">SQL Tools</a> <span>(63 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/ssis/">SSIS</a> <span>(15 articles)</span></div><img style="cursor:default;vertical-align:middle;"></img><br><div><img style="cursor:default;vertical-align:middle;"></img>  <a href="http://www.simple-talk.com/sql/reporting-services/">Reporting Services</a> <span>(18 articles)</span></div></div><div><a href="http://www.simple-talk.com/author/phil-factor/"><img height="50" style="cursor:default;vertical-align:middle;" title="Phil Factor - the wilder shores of transact sql" width="315"></img></a></div><div><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br><a href="http://www.simple-talk.com/author/phil-factor/" title="Phil Factor"><img align="left" alt="Phil Factor" style="cursor:default;vertical-align:middle;"></img></a><div><strong><a href="http://www.simple-talk.com/sql/learn-sql-server/how-to-import-data-from-html-pages/">How to Import Data from HTML pages</a></strong><br><img height="9" style="cursor:default;vertical-align:middle;" width="10"></img> It turns out that there are plenty of ways to get data into SQL Server from websites, whether the data is in...<img height="9" style="cursor:default;vertical-align:middle;" width="10"></img> <a href="http://www.simple-talk.com/sql/learn-sql-server/how-to-import-data-from-html-pages/">Read more...</a><br><br><br></div><div>RECENT BLOG POSTS:<br><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> <a href="http://www.simple-talk.com/community/blogs/philfactor/101094.aspx">A brief nod at reality: Who was that masked...</a><br><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> <a href="http://www.simple-talk.com/community/blogs/philfactor/99400.aspx">The Presentation Isn't Over Until It's Over</a><br><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> <a href="http://www.simple-talk.com/community/blogs/philfactor/98746.aspx">IsNumeric() Broken? Only up to a point.</a><br><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br></div><img height="8" style="cursor:default;vertical-align:middle;" width="1"></img><br><img height="15" style="cursor:default;vertical-align:middle;" width="13"></img> <a href="http://www.simple-talk.com/community/blogs/philfactor/default.aspx">View the blog</a><br></div><div><a><img height="30" style="cursor:default;vertical-align:middle;" title="Phil Factor - the wilder shores of transact sql" width="300"></img></a></div><div><strong><a href="http://www.simple-talk.com/sql/learn-sql-server/how-to-import-data-from-html-pages/">How to Import Data from HTML pages</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> It turns out that there are plenty of ways to get data into SQL Server from websites, whether the data... <a href="http://www.simple-talk.com/sql/learn-sql-server/how-to-import-data-from-html-pages/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/database-administration/simple-database-backups-with-sql-azure/">Simple Database Backups With SQL Azure</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> SQL Azure can take away a great deal of the maintenance work from a hosted database-driven website. It... <a href="http://www.simple-talk.com/sql/database-administration/simple-database-backups-with-sql-azure/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/sql-tools/continuous-integration-for-sql-server-databases/">Continuous Integration for SQL Server Databases</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> When it comes to the subject of putting databases into source control, Troy Hunt is no shrinking... <a href="http://www.simple-talk.com/sql/sql-tools/continuous-integration-for-sql-server-databases/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/sql-tools/sql-source-control-the-development-story-/">SQL Source Control: The Development Story</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> Often, there is a huge difference between software being easy to use, and easy to develop. When your... <a href="http://www.simple-talk.com/sql/sql-tools/sql-source-control-the-development-story-/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/sql-tools/sql-scripts-manager-an-appreciation/">SQL Scripts Manager: An Appreciation</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> SQL Scripts Manager is Simple-Talk's present to its readers. William Brewer was an enthusiastic... <a href="http://www.simple-talk.com/sql/sql-tools/sql-scripts-manager-an-appreciation/">Read more...</a><br><br></div><div><a><img height="30" style="cursor:default;vertical-align:middle;" title="Phil Factor - the wilder shores of transact sql" width="300"></img></a></div><div><strong><a href="http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-1/">Beginning SQL Server 2005 Reporting Services Part 1</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... <a href="http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-1/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/">Ten Common Database Design Mistakes</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> Database design and implementation is the cornerstone of any data centric project (read 99.9% of... <a href="http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-2/">Beginning SQL Server 2005 Reporting Services Part 2</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> Continuing his in-depth tour of SQL Server 2005 Reporting Services, Steve Joubert demonstrates the most... <a href="http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-2/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/">Reading and Writing Files in SQL Server using T-SQL</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> SQL Server provides several &quot;standard&quot; techniques by which to read and write to files but, just... <a href="http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/">Read more...</a><br><br><strong><a href="http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/">Creating CSV Files Using BCP and Stored Procedures</a></strong><br><img height="11" style="cursor:default;vertical-align:middle;" width="9"></img> Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing... <a href="http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/">Read more...</a><br><br></div><div><a href="http://www.simple-talk.com/community/user/CreateUser.aspx?ReturnUrl=%2fsql%2fdatabase-administration%2fcreating-csv-files-using-bcp-and-stored-procedures%2f"><img height="50" style="cursor:default;vertical-align:middle;" title="Join Simple Talk" width="315"></img></a></div><div><a href="http://www.simple-talk.com/community/user/CreateUser.aspx?ReturnUrl=%2fsql%2fdatabase-administration%2fcreating-csv-files-using-bcp-and-stored-procedures%2f"><img align="left" height="43" style="cursor:default;vertical-align:middle;" title="Join Simple Talk" width="54"></img></a> Over 150,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.<br><br><div><a href="http://www.simple-talk.com/community/user/CreateUser.aspx?ReturnUrl=%2fsql%2fdatabase-administration%2fcreating-csv-files-using-bcp-and-stored-procedures%2f"><img alt="Join Simple Talk" height="19" style="cursor:default;vertical-align:middle;" width="117"></img></a></div></div><br></div><br><div><a href="http://www.simple-talk.com/privacy-policy.aspx">Privacy policy</a> | <a href="http://www.simple-talk.com/terms-and-conditions.aspx">Terms and conditions</a> | <a href="http://www.red-gate.com/" target="_blank">©2005-2011 Red Gate Software</a></div><div><a href="http://www.simple-talk.com/about.aspx">About</a> | <a href="http://www.simple-talk.com/site-map.aspx">Site map</a> | <a href="http://www.simple-talk.com/become-an-author.aspx">Become an author</a> | <a href="http://www.simple-talk.com/newsletter-archive/">Newsletters</a> | <a href="http://www.simple-talk.com/contact-us.aspx">Contact us</a></div></div></div></html></body> 